package com.wyy.dao.impl;

import com.wyy.bean.Student;
import com.wyy.dao.DBUtils;
import com.wyy.dao.StudentDao;
import com.wyy.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName StudentDaoImpl
 * @Description //TODO
 * @Author wky
 * @Date 2021/9/12 14:52
 * @Version 1.0
 **/
public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudents(String name,String stuno,int sex,int pageIndex,int pageSize) {
        List<Student> list = new ArrayList<>();
        List params = new ArrayList();
        try {
            StringBuffer sqlBuf = new StringBuffer("select * from student where 1=1 and state != "+StudentEnum.DELETE.type);
            if (name!=null && name.length()>0){
                sqlBuf.append(" and stuname like ?");
                params.add("%"+name+"%");
            }
            if (stuno!=null && stuno.length()>0){
                sqlBuf.append(" and stuno = ?");
                params.add(stuno);
            }
            if (sex != -1){
                sqlBuf.append(" and sex = ?");
                params.add(sex);
            }
            sqlBuf.append(" limit ?,?");
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);
            System.out.println("---------->"+sqlBuf);
            System.out.println("--pageIndex-------->"+pageIndex);
            System.out.println("--pageSize--------->"+pageSize);
            resultSet = query(sqlBuf.toString(), params);
            while (resultSet.next()){
                Student stu = new Student();
                stu.setStuId(resultSet.getInt("stuid"));
                stu.setStuNo(resultSet.getString("stuno"));
                stu.setStuName(resultSet.getString("stuname"));
                stu.setSex(resultSet.getInt("sex"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setProfession(resultSet.getString("profession"));
                stu.setRegDate(resultSet.getDate("regdate"));
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }
    
    @Override
    public int total(String name, String stuno, int sex) {
        int total = 0;
        List params = new ArrayList();
    
        try {
            StringBuffer sqlBuf = new StringBuffer("select count(*) from student where 1=1 and state != "+StudentEnum.DELETE.type);
            if (name != null && name.length() > 0) {
                sqlBuf.append(" and stuname like ?");
                params.add("%" + name + "%");
            }
            if (stuno != null && stuno.length() > 0) {
                sqlBuf.append(" and stuno = ?");
                params.add(stuno);
            }
            if (sex != -1) {
                sqlBuf.append(" and sex = ?");
                params.add(sex);
            }
            resultSet = query(sqlBuf.toString(),params);
            while(resultSet.next()){
                total = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
    
        return total;
    }
    
    @Override
    public int insertStu(Student student) {
        int update = 0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList<>();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
    
    @Override
    public Student findById(int sid) {
        Student stu = null;
        try {
            String sql = "select * from student where stuid = ?";
            List params = new ArrayList<>();
            params.add(sid);
            resultSet = query(sql, params);
            while(resultSet.next()){
                stu=new Student();
                stu = new Student();
                stu.setStuId(resultSet.getInt("stuid"));
                stu.setStuNo(resultSet.getString("stuno"));
                stu.setStuName(resultSet.getString("stuname"));
                stu.setSex(resultSet.getInt("sex"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setProfession(resultSet.getString("profession"));
                stu.setRegDate(resultSet.getDate("regdate"));
                stu.setEmail(resultSet.getString("email"));
                stu.setIntroduction(resultSet.getString("introduction"));
                stu.setGid(resultSet.getInt("gid"));
                stu.setRegistered(resultSet.getString("registered"));
                stu.setAddress(resultSet.getString("address"));
                stu.setIdNumber(resultSet.getString("idnumber"));
                stu.setPolitics(resultSet.getString("politics"));
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return stu;
    }
    
    @Override
    public Student findByStuNo(String stuNo) {
        Student stu = null;
        try {
            String sql = "select * from student where stuno = ?";
            List params = new ArrayList<>();
            params.add(stuNo);
            resultSet = query(sql, params);
            while(resultSet.next()){
                stu=new Student();
                stu = new Student();
                stu.setStuId(resultSet.getInt("stuid"));
                stu.setStuNo(resultSet.getString("stuno"));
                stu.setStuName(resultSet.getString("stuname"));
                stu.setSex(resultSet.getInt("sex"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setProfession(resultSet.getString("profession"));
                stu.setRegDate(resultSet.getDate("regdate"));
                stu.setEmail(resultSet.getString("email"));
                stu.setIntroduction(resultSet.getString("introduction"));
                stu.setGid(resultSet.getInt("gid"));
                stu.setRegistered(resultSet.getString("registered"));
                stu.setAddress(resultSet.getString("address"));
                stu.setIdNumber(resultSet.getString("idnumber"));
                stu.setPolitics(resultSet.getString("politics"));
            
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return stu;
    }
    
    @Override
    public int updateStu(Student student) {
    
        int update = 0;
        try {
            String sql = "update student set " +
                    "stuname = ?," +
                    "stuno = ?," +
                    "sex = ?," +
                    "phone = ?," +
                    "email = ?," +
                    "registered = ?," +
                    "address = ?," +
                    "profession = ?," +
                    "idnumber = ?," +
                    "politics = ?," +
                    "introduction = ?," +
                    "gid = ?" +
                    " where stuid = ?";
            List params = new ArrayList();
        
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
//            System.out.println("2---"+student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuId());
        
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
    
    @Override
    public int delStu(String sid) {
    
        int update = 0;
        try {
            String sql = "update student set state = ? where stuid = ?";
            List params = new ArrayList();
            params.add(StudentEnum.DELETE.type);
            params.add(sid);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
    
        return update;
    }
}
